
Partial Class Presentation_Management_HotClients
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Sql As String
        Dim CountSql As String
        Dim Params(0) As GenParameter
        Dim SortOrder As String

        Page.Title = "Hot Clients"

        'Select the sort order

        Select Case SelectSort.SelectedValue
            Case "ProjectSort"

                SortOrder = " ORDER BY ProjectMaster.ProjectName, ExecutiveMaster.ExecutiveName, ClientName"
            Case "ExecutiveSort"
                SortOrder = " ORDER BY ExecutiveMaster.ExecutiveName, ProjectMaster.ProjectName, ClientName"
            Case Else
                SortOrder = " ORDER BY ProjectMaster.ProjectName, ExecutiveMaster.ExecutiveName, ClientName"

        End Select




        If Not User.IsInRole("Top Management") Then

            Sql = "Select ExecutiveName, ClientName, ProjectMaster.ProjectName,StatusDescription, DATE_FORMAT(W.MeetingDate,'%M %d %Y') AS 'Meeting Date' FROM ((((WorkflowMaster AS W INNER JOIN ProjectMaster ON W.ProjectID = ProjectMaster.ProjectID) INNER JOIN StatusMaster ON W.StatusID = StatusMaster.StatusID) INNER JOIN ExecutiveMaster ON ExecutiveMaster.ExecutiveID = W.ExecutiveID) INNER JOIN ClientMaster ON W.ClientID = ClientMaster.ClientID)," & _
            " (Select ClientID, Max(MeetingDate) AS 'MaxDate' FROM WorkflowMaster GROUP BY ClientID) AS ClientLastDate" & _
            " WHERE W.ClientID = ClientLastDate.ClientID AND W.MeetingDate = ClientLastDate.MaxDate AND (StatusDescription = 'Hot' OR StatusDescription = 'Prospective') AND Location = ?Location AND ProjectMaster.Completed = 0" & _
            SortOrder

            CountSql = "SELECT COUNT(*) FROM (Select ExecutiveName, ClientName, ProjectMaster.ProjectName,StatusDescription, DATE_FORMAT(W.MeetingDate,'%M %d %Y') AS 'Meeting Date' FROM ((((WorkflowMaster AS W INNER JOIN ProjectMaster ON W.ProjectID = ProjectMaster.ProjectID) INNER JOIN StatusMaster ON W.StatusID = StatusMaster.StatusID) INNER JOIN ExecutiveMaster ON ExecutiveMaster.ExecutiveID = W.ExecutiveID) INNER JOIN ClientMaster ON W.ClientID = ClientMaster.ClientID)," & _
            " (Select ClientID, Max(MeetingDate) AS 'MaxDate' FROM WorkflowMaster GROUP BY ClientID) AS ClientLastDate" & _
            " WHERE W.ClientID = ClientLastDate.ClientID AND W.MeetingDate = ClientLastDate.MaxDate AND (StatusDescription = 'Hot' OR StatusDescription = 'Prospective') AND Location = ?Location AND ProjectMaster.Completed = 0) AS temp"

            Params(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)

        Else

            Sql = "Select ExecutiveName, ClientName, ProjectMaster.ProjectName,StatusDescription, DATE_FORMAT(W.MeetingDate,'%M %d %Y') AS 'Meeting Date' FROM ((((WorkflowMaster AS W INNER JOIN ProjectMaster ON W.ProjectID = ProjectMaster.ProjectID) INNER JOIN StatusMaster ON W.StatusID = StatusMaster.StatusID) INNER JOIN ExecutiveMaster ON ExecutiveMaster.ExecutiveID = W.ExecutiveID) INNER JOIN ClientMaster ON W.ClientID = ClientMaster.ClientID)," & _
           " (Select ClientID, Max(MeetingDate) AS 'MaxDate' FROM WorkflowMaster GROUP BY ClientID) AS ClientLastDate" & _
           " WHERE W.ClientID = ClientLastDate.ClientID AND W.MeetingDate = ClientLastDate.MaxDate AND (StatusDescription = 'Hot' OR StatusDescription = 'Prospective') AND ProjectMaster.Completed = 0" & _
           SortOrder

            CountSql = "SELECT COUNT(*) FROM (Select ExecutiveName, ClientName, ProjectMaster.ProjectName,StatusDescription, DATE_FORMAT(W.MeetingDate,'%M %d %Y') AS 'Meeting Date' FROM ((((WorkflowMaster AS W INNER JOIN ProjectMaster ON W.ProjectID = ProjectMaster.ProjectID) INNER JOIN StatusMaster ON W.StatusID = StatusMaster.StatusID) INNER JOIN ExecutiveMaster ON ExecutiveMaster.ExecutiveID = W.ExecutiveID) INNER JOIN ClientMaster ON W.ClientID = ClientMaster.ClientID)," & _
           " (Select ClientID, Max(MeetingDate) AS 'MaxDate' FROM WorkflowMaster GROUP BY ClientID) AS ClientLastDate" & _
           " WHERE W.ClientID = ClientLastDate.ClientID AND W.MeetingDate = ClientLastDate.MaxDate AND (StatusDescription = 'Hot' OR StatusDescription = 'Prospective') AND ProjectMaster.Completed = 0) AS temp"


            Params(0) = New GenParameter("?Location", Request.Cookies("Profile").Item("Location"), Data.DbType.String)

        End If

        

        cstmHot.Sql = Sql
        cstmHot.CountSql = CountSql
        cstmHot.Params = Params
        cstmHot.ShowButton = True
        cstmHot.ShowImmediately = True
    End Sub
End Class
